const { json } = require('express');
const connection = require('../database/mysql')

//增添数据
module.exports.iUAPTable = function(username,password,nickname,picurl){
    var sql = 'insert into UAPTable(username,password,nickname,picurl) values (?,?,?,?)';
    connection.query(sql,[username,password,nickname,picurl],function(err,result){
        if(err) console.log(err.message);
        else console.log("插入UAPTable成功");
    })
}
module.exports.iPublishTable = function(username,nickname,tasknum,tag,taskinfo,picurl,begintime,endtime,status,contact,picnum){
    var sql = 'insert into PublishTable(username,nickname,tasknum,tag,taskinfo,picurl,begintime,endtime,status,contact,picnum) values (?,?,?,?,?,?,?,?,?,?,?)';
    connection.query(sql,[username,nickname,tasknum,tag,taskinfo,picurl,begintime,endtime,status,contact,picnum],function(err,result){
        if(err) console.log(err.message);
        else console.log("插入PublishTable成功");
    })
}
module.exports.iPushTable = function(username,tasknum,picurl,status){
    var sql = 'insert into PushTable(username,tasknum,picurl,status) values (?,?,?,?)';
    connection.query(sql,[username,tasknum,picurl,status],function(err,result){
        if(err) console.log(err.message);
        else console.log("插入PushTable成功");
    })
}
module.exports.iAccessTable = function(username,tasknum){
    var sql = 'insert into AccessTable(username,tasknum) values (?,?)';
    connection.query(sql,[username,tasknum],function(err,result){
        if(err) console.log(err.message);
        else console.log("插入AccessTable成功");
    })
}

//删除数据
module.exports.dUAPTable = function(username){
    var sql = 'delete from UAPTable where username="'+username+'"';
    connection.query(sql,(err,result) =>{
        if(err) return console.log(err.message);
        else console.log("删除UAPTable一条数据成功");
    })
}
module.exports.dPublishTable = function(username,tasknum){
    var sql = 'delete from PublishTable where username="'+username+'"&&tasknum="'+tasknum+'"';
    connection.query(sql,(err,result) =>{
        if(err) return console.log(err.message);
        else console.log("删除PublishTable一条数据成功");
    })
}
module.exports.dPushTable = function(picurl){
    var sql = 'delete from PushTable where picurl="'+picurl+'"';
    connection.query(sql,(err,result) =>{
        if(err) return console.log(err.message);
        else console.log("删除PushTable一条数据成功");
    })
}
module.exports.dAccessTable = function(username,tasknum){
    var sql = 'delete from AccessTable where username="'+username+'"&&tasknum='+tasknum+'"';
    connection.query(sql,(err,result) =>{
        if(err) return console.log(err.message);
        else console.log("删除AccessTable一条数据成功");
    })
}

//修改数据
module.exports.uUAPTable = function(key,value,username){
    var sql = 'update UAPTable set '+key+'="'+value+'"where username="'+username+'"';
    connection.query(sql,(err,result) =>{
        if(err) return console.log(err.message);
        else console.log("修改UAPTable一条数据成功");
    })
}
module.exports.uPublishTable = function(key,value,tasknum){
    var sql = 'update PublishTable set '+key+'="'+value+'"where tasknum="'+tasknum+'"';
    connection.query(sql,(err,result) =>{
        if(err) return console.log(err.message);
        else console.log("修改PublishTable一条数据成功");
    })
}
module.exports.uPushTable = function(key,value,picurl){
    var sql = 'update PushTable set '+key+'="'+value+'"where picurl="'+picurl+'"';
    connection.query(sql,(err,result) =>{
        if(err) return console.log(err.message);
        else console.log("修改PushTable一条数据成功");
    })
}
module.exports.uAccessTable = function(key,value,username,tasknum){
    var sql = 'update AccessTable set '+key+'="'+value+'"where username="'+username+'"&&tasknum="'+tasknum+'"';
    connection.query(sql,(err,result) =>{
        if(err) return console.log(err.message);
        else console.log("修改AccessTable一条数据成功");
    })
}

//查询数据
module.exports.sUAPTable = async function(username){
    var sql = 'select * from UAPTable where username="'+username+'"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sUAPTableUsername = async function(){
    var sql = 'select username from UAPTable';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sUAPTableBalance = async function(username){
    var sql = 'select balance from UAPTable where username="'+username+'"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPublishTableByUsername = async function(username){
    var sql = 'select * from PublishTable where username="'+username+'"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPublishTableByNickname = async function(nickname){
    var sql = 'select * from PublishTable where status="进行中"&&nickname like \'%' + nickname + '%\'';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPublishTableById = async function(id){
    var sql = 'select * from PublishTable where tasknum="'+id+'"&&status="进行中"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPublishTableById2 = async function(id){
    var sql = 'select * from PublishTable where tasknum="'+id+'"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPublishTableByTag = async function(tag){
    var sql = 'select * from PublishTable where status="进行中"&&tag like \'%' + tag + '%\'';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPublishTableAll = async function(){
    var sql = 'select * from PublishTable';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPublishTableAll2 = async function(){
    var sql = 'select * from PublishTable where status="进行中"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPushTable = async function(username){
    var sql = 'select * from PushTable where username="'+username+'"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPushTableBytasknum = async function(tasknum){
    var sql = 'select * from PushTable where tasknum="'+tasknum+'"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sPushTableBytasknumAndusername = async function(username,tasknum){
    var sql = 'select * from PushTable where tasknum="'+tasknum+'"&&username="'+username+'"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}
module.exports.sAccessTable = async function(username){  //查询某个用户的所有任务号
    var sql = 'select tasknum from AccessTable where username="'+username+'"';
     return new Promise((res, rej) => {
        connection.query(sql,(err,result)=>{
            if(err) return console.log(err.message);
            res(JSON.parse(JSON.stringify(result)))
        });
    })
}